set doc off
set verify off
--
--    Program: CAPGLEXT_AUTOPOST.sql
--    Description:
--       Extract auto post settings associated with the SOB that the user specified
--
--    Revision:
--       1st     
--    Parameter: 1. set of books short name e.g. SOB_C999

--whenever sqlerror exit failure rollback
set serveroutput on size 1000000

declare   
   l_processed_count_s		number;
   l_rejected_count_s 		number;
   l_processed_count_o		number;
   l_rejected_count_o 		number;
   l_err_msg				varchar2(2000);
                    		
   l_utl_file_1       		utl_file.file_type;
   l_utl_file_2       		utl_file.file_type;
   l_utl_file_3       		utl_file.file_type;
   l_filename_1       		varchar2(255);
   l_filename_2       		varchar2(255);
   l_filename_3       		varchar2(255);
   l_pathname       		varchar2(100);
                    		
   l_ngo_sob				varchar2(20);
   
   cursor cur_s (p_ngo_sob varchar2) is
     select * 
     from   gl_automatic_posting_sets 
     where  set_of_books_id = (select set_of_books_id 
                               from   gl_sets_of_books 
                               where  upper(short_name) = upper(p_ngo_sob));

   cursor cur_o (p_autopost_set_id number) is
     select * 
     from   gl_automatic_posting_options 
     where  autopost_set_id = p_autopost_set_id;



begin
   l_pathname := '/tmp';
   l_filename_1 := 'CAPGL_AUTOPOST_SET.dat';
   l_filename_2 := 'CAPGL_AUTOPOST_OPTION.dat';
   l_utl_file_1 := utl_file.fopen(l_pathname, l_filename_1, 'w');
   l_utl_file_2 := utl_file.fopen(l_pathname, l_filename_2, 'w');
   
   l_processed_count_s := 0;
   l_rejected_count_s  := 0;
   l_processed_count_o := 0;
   l_rejected_count_o  := 0;
   
   l_ngo_sob := '&1';
       
   for s in cur_s(l_ngo_sob) loop
     utl_file.put_line( l_utl_file_1, 
       s.autopost_set_id|| chr(9) ||
       s.set_of_books_id|| chr(9) ||
       s.autopost_set_name|| chr(9) ||
       s.submit_all_priorities_flag|| chr(9) ||
       s.enabled_flag|| chr(9) ||
       to_char(s.last_update_date, 'DD-MON-YYYY HH24:MI:SS')|| chr(9) ||
       s.last_updated_by|| chr(9) ||
       to_char(s.creation_date, 'DD-MON-YYYY HH24:MI:SS')|| chr(9) ||
       s.created_by|| chr(9) ||
       s.last_update_login|| chr(9) ||
       s.description|| chr(9) ||
       s.num_of_priority_options|| chr(9) ||
       s.effective_days_before|| chr(9) ||
       s.effective_days_after|| chr(9) ||
       s.attribute1|| chr(9) ||
       s.attribute2|| chr(9) ||
       s.attribute3|| chr(9) ||
       s.attribute4|| chr(9) ||
       s.attribute5|| chr(9) ||
       s.attribute6|| chr(9) ||
       s.attribute7|| chr(9) ||
       s.attribute8|| chr(9) ||
       s.attribute9|| chr(9) ||
       s.attribute10|| chr(9) ||
       s.attribute11|| chr(9) ||
       s.attribute12|| chr(9) ||
       s.attribute13|| chr(9) ||
       s.attribute14|| chr(9) ||
       s.attribute15|| chr(9) ||
       s.context|| chr(9) ||
       CAPBREXT_COMMON.GetUser(s.last_updated_by)|| chr(9) ||
       CAPBREXT_COMMON.GetUser(s.created_by)
     );
     
     l_processed_count_s := l_processed_count_s + 1;
     
     for o in cur_o(s.autopost_set_id) loop         
             
       utl_file.put_line( l_utl_file_2, 
         o.autopost_set_id|| chr(9) ||
         o.actual_flag|| chr(9) ||
         o.period_name|| chr(9) ||
         o.je_source_name|| chr(9) ||
         o.je_category_name|| chr(9) ||
         o.posting_priority|| chr(9) ||
         to_char(o.last_update_date, 'DD-MON-YYYY HH24:MI:SS')|| chr(9) ||
         o.last_updated_by|| chr(9) ||
         to_char(o.creation_date, 'DD-MON-YYYY HH24:MI:SS')|| chr(9) ||
         o.created_by|| chr(9) ||
         o.last_update_login|| chr(9) ||
         o.attribute1|| chr(9) ||
         o.attribute2|| chr(9) ||
         o.attribute3|| chr(9) ||
         o.attribute4|| chr(9) ||
         o.attribute5|| chr(9) ||
         o.attribute6|| chr(9) ||
         o.attribute7|| chr(9) ||
         o.attribute8|| chr(9) ||
         o.attribute9|| chr(9) ||
         o.attribute10|| chr(9) ||
         o.attribute11|| chr(9) ||
         o.attribute12|| chr(9) ||
         o.attribute13|| chr(9) ||
         o.attribute14|| chr(9) ||
         o.attribute15|| chr(9) ||
         o.context|| chr(9) ||
         CAPBREXT_COMMON.GetUser(o.last_updated_by)|| chr(9) ||
         CAPBREXT_COMMON.GetUser(o.created_by)
         );
       
       l_processed_count_o := l_processed_count_o + 1;       
     end loop;
     
   end loop;       
   
   l_err_msg := '';
   if l_processed_count_s = 0 then
     l_rejected_count_s := l_rejected_count_s + 1;
     l_err_msg := 'No Auto Post Rules defined for this NGO '||l_ngo_sob;
   end if;
      
   -- insert into log table
   insert into capcnv_log ( prog_id, process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPGLEXT_AUTOPOST', sysdate, l_processed_count_s, l_rejected_count_s, l_processed_count_s-l_rejected_count_s, l_err_msg );
   
   l_err_msg := '';
   if l_processed_count_o = 0 then
     l_rejected_count_o := l_rejected_count_o + 1;
     l_err_msg := 'No Auto Post Options defined for this NGO '||l_ngo_sob;
   end if;
        
   insert into capcnv_log ( prog_id, process_date, record_processed,record_rejected,record_extracted,err_msg) 
   values ('CAPGLEXT_AUTOPOST', sysdate, l_processed_count_o, l_rejected_count_o, l_processed_count_o-l_rejected_count_o, l_err_msg );        
       
   commit;

   utl_file.fclose(l_utl_file_1);
   utl_file.fclose(l_utl_file_2);   
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;
end;
/

--exit
